/**
 * 测试数据库连接脚本
 * 用于验证主数据库和备份数据库的连接状态
 */

const mysql = require('mysql2/promise');

// 数据库配置
const SOURCE_DB = {
  host: '43.143.253.188',
  port: 3306,
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4'
};

const BACKUP_DB = {
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: 'jack666',
  database: 'test2_nextjs',
  charset: 'utf8mb4'
};

class DatabaseConnectionTester {
  constructor() {
    this.connections = {};
  }

  async testConnection(name, config) {
    console.log(`\n🔄 测试 ${name} 数据库连接...`);
    console.log(`   地址: ${config.host}:${config.port}`);
    console.log(`   数据库: ${config.database}`);
    console.log(`   用户: ${config.user}`);
    
    try {
      const connection = await mysql.createConnection(config);
      
      // 测试基本查询
      const [result] = await connection.execute('SELECT 1 as test');
      console.log(`   ✅ 连接成功，测试查询结果: ${result[0].test}`);
      
      // 获取数据库版本
      const [version] = await connection.execute('SELECT VERSION() as version');
      console.log(`   📊 MySQL版本: ${version[0].version}`);
      
      // 获取数据库大小
      const [size] = await connection.execute(`
        SELECT 
          ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
        FROM information_schema.tables 
        WHERE table_schema = ?
      `, [config.database]);
      console.log(`   💾 数据库大小: ${size[0].size_mb} MB`);
      
      // 获取表数量
      const [tables] = await connection.execute(`
        SELECT COUNT(*) as count 
        FROM information_schema.tables 
        WHERE table_schema = ? AND table_type = 'BASE TABLE'
      `, [config.database]);
      console.log(`   📋 表数量: ${tables[0].count}`);
      
      await connection.end();
      return { success: true, connection };
      
    } catch (error) {
      console.error(`   ❌ 连接失败: ${error.message}`);
      
      if (error.code === 'ECONNREFUSED') {
        console.error(`   💡 连接被拒绝，请检查：`);
        console.error(`      - 数据库服务是否正在运行`);
        console.error(`      - 地址和端口是否正确`);
        console.error(`      - 防火墙是否阻止了连接`);
      } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
        console.error(`   💡 访问被拒绝，请检查：`);
        console.error(`      - 用户名和密码是否正确`);
        console.error(`      - 用户是否有足够的权限`);
      } else if (error.code === 'ER_BAD_DB_ERROR') {
        console.error(`   💡 数据库不存在，请检查：`);
        console.error(`      - 数据库名称是否正确`);
        console.error(`      - 数据库是否已创建`);
      }
      
      return { success: false, error: error.message };
    }
  }

  async testSourceDatabase() {
    return await this.testConnection('源数据库（主数据库）', SOURCE_DB);
  }

  async testBackupDatabase() {
    return await this.testConnection('备份数据库（本地）', BACKUP_DB);
  }

  async testAllConnections() {
    console.log('🔍 开始测试数据库连接...\n');
    
    const sourceResult = await this.testSourceDatabase();
    const backupResult = await this.testBackupDatabase();
    
    console.log('\n📊 连接测试结果:');
    console.log(`   源数据库: ${sourceResult.success ? '✅ 成功' : '❌ 失败'}`);
    console.log(`   备份数据库: ${backupResult.success ? '✅ 成功' : '❌ 失败'}`);
    
    if (sourceResult.success && backupResult.success) {
      console.log('\n🎉 所有数据库连接正常，可以开始同步！');
      return true;
    } else {
      console.log('\n⚠️ 部分数据库连接失败，请检查配置后重试。');
      return false;
    }
  }

  async getTableList(config, name) {
    try {
      console.log(`\n📋 获取 ${name} 数据库表列表...`);
      
      const connection = await mysql.createConnection(config);
      
      const [tables] = await connection.execute(`
        SELECT 
          TABLE_NAME,
          TABLE_ROWS,
          ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
        FROM information_schema.tables 
        WHERE table_schema = ? AND table_type = 'BASE TABLE'
        ORDER BY TABLE_NAME
      `, [config.database]);
      
      console.log(`   发现 ${tables.length} 个表:`);
      tables.forEach((table, index) => {
        console.log(`   ${index + 1}. ${table.TABLE_NAME} (${table.TABLE_ROWS}行, ${table.size_mb}MB)`);
      });
      
      await connection.end();
      return tables;
      
    } catch (error) {
      console.error(`   ❌ 获取表列表失败: ${error.message}`);
      return [];
    }
  }

  async compareDatabases() {
    console.log('\n🔍 比较两个数据库的表结构...');
    
    try {
      const sourceConnection = await mysql.createConnection(SOURCE_DB);
      const backupConnection = await mysql.createConnection(BACKUP_DB);
      
      // 获取源数据库表列表
      const [sourceTables] = await sourceConnection.execute(`
        SELECT TABLE_NAME FROM information_schema.tables 
        WHERE table_schema = ? AND table_type = 'BASE TABLE'
        ORDER BY TABLE_NAME
      `, [SOURCE_DB.database]);
      
      // 获取备份数据库表列表
      const [backupTables] = await backupConnection.execute(`
        SELECT TABLE_NAME FROM information_schema.tables 
        WHERE table_schema = ? AND table_type = 'BASE TABLE'
        ORDER BY TABLE_NAME
      `, [BACKUP_DB.database]);
      
      const sourceTableNames = sourceTables.map(t => t.TABLE_NAME);
      const backupTableNames = backupTables.map(t => t.TABLE_NAME);
      
      console.log(`   源数据库表数量: ${sourceTableNames.length}`);
      console.log(`   备份数据库表数量: ${backupTableNames.length}`);
      
      // 找出差异
      const onlyInSource = sourceTableNames.filter(name => !backupTableNames.includes(name));
      const onlyInBackup = backupTableNames.filter(name => !sourceTableNames.includes(name));
      const common = sourceTableNames.filter(name => backupTableNames.includes(name));
      
      console.log(`   共同表数量: ${common.length}`);
      console.log(`   仅在源数据库: ${onlyInSource.length}`);
      console.log(`   仅在备份数据库: ${onlyInBackup.length}`);
      
      if (onlyInSource.length > 0) {
        console.log('\n   仅在源数据库中的表:');
        onlyInSource.forEach(name => console.log(`     - ${name}`));
      }
      
      if (onlyInBackup.length > 0) {
        console.log('\n   仅在备份数据库中的表:');
        onlyInBackup.forEach(name => console.log(`     - ${name}`));
      }
      
      await sourceConnection.end();
      await backupConnection.end();
      
    } catch (error) {
      console.error(`❌ 比较数据库失败: ${error.message}`);
    }
  }
}

// 主函数
async function main() {
  const tester = new DatabaseConnectionTester();
  
  try {
    // 测试连接
    const allConnected = await tester.testAllConnections();
    
    if (allConnected) {
      // 获取表列表
      await tester.getTableList(SOURCE_DB, '源数据库');
      await tester.getTableList(BACKUP_DB, '备份数据库');
      
      // 比较数据库
      await tester.compareDatabases();
    }
    
  } catch (error) {
    console.error('❌ 测试失败:', error);
    process.exit(1);
  }
}

// 处理程序退出
process.on('SIGINT', () => {
  console.log('\n🛑 测试被中断');
  process.exit(0);
});

// 运行主函数
if (require.main === module) {
  main();
}

module.exports = DatabaseConnectionTester;
